﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using HZ.Supervise.Model.View;
namespace HZ.Supervise.DAL
{
    public class DAL_CompanyOrderDetail
    {
        /// <summary>
        /// 企业配送统计
        /// </summary>
        /// <param name="nsort"> 0 按类别分组，1 按月份分组,2 按地区统计（地州）,3 按月份 类别 生产企业，4 按月份 配送企业 </param>
        /// <returns></returns>
        public static DataTable GetCompanySC_sort(int nsort, string where,string order, int currentpage, int pagesize, out int totalrecord)
        {
            string strsql = "";
            switch (nsort)
            {
                case 0: //strsql
                    strsql = "select SortName,Source,SUM(PuchaseCount)  PuchaseCount ,SUM(DistributeCount) DistributeCount from View_CompanyOrderDetail where 1=1 " + where + "  group by SortName,Source order by SortName asc";
                    break;
                case 1://
                    strsql = "select Source, dbo.fun_GetYearMonth(AddTime) addtime,SUM(PuchaseCount) PuchaseCount ,SUM(DistributeCount) DistributeCount from View_CompanyOrderDetail where 1=1 group by  dbo.fun_GetYearMonth(AddTime),Source order by dbo.fun_GetYearMonth(AddTime) ";
                    break;
                case 2:
                    strsql = " SELECT B.AreaID,B.PurchaseTimeMonth,B.SortSource,b.SortName,PurchaseCountTotal, PurchaseAmountTotal, WareHouseCountTotal, WareHouseRate, WareHouseAmountTotal, Profit,AreaName FROM ( SELECT AreaID,dbo.fun_GetYearMonth(PlanTime) AS PurchaseTimeMonth,SortSource AS SortSource,max(SortName) AS SortName,SUM(PurchaseAmount) AS PurchaseAmountTotal,SUM(PurchaseCount) AS PurchaseCountTotal,SUM(WarehouseCount) AS WareHouseCountTotal,Case when sum(WareHouseCount)=0 then null when sum(PurchaseCount)=0 then NULL else CONVERT(FLOAT,SUM(WareHouseAmount)) / SUM(PurchaseAmount) end  AS WareHouseRate,SUM(WarehouseAmount) AS WareHouseAmountTotal,COUNT (DISTINCT ProcureCatalogID) AS Profit FROM View_Supervise WHERE 1=1 AND (PlanTime>='2010-08-01' and PlanTime<'2010-09-01') and AreaID like '%5301%'  GROUP BY AreaID,dbo.fun_GetYearMonth(PlanTime),SortSource) AS B  INNER JOIN TB_Area ON TB_Area.AreaID = B.AreaID ORDER BY PurchaseTimeMonth ASC";
                    break;
                case 3:
                    strsql = "select Source,Sellname, dbo.fun_GetYearMonth(AddTime) addtime,SUM(PuchaseCount) PuchaseCount ,SUM(DistributeCount) DistributeCount from View_CompanyOrderDetail where 1=1 and " + where + " group by  dbo.fun_GetYearMonth(AddTime),Source,Sellname order by "+order;
                    break;
                case 4:
                    strsql = @" SELECT  A.Source,A.motch,isnull(A.PuchaseCountSum,0) PuchaseCountSum, isnull(A.WareHouseCount,0) WareHouseCount, isnull(B.PuchaseCountAmountHospital,0) PuchaseCountAmountHospital,isnull(B.PuchaseCountSumHospital,0) PuchaseCountSumHospital, isnull(c.StockCount,0)  StockCount FROM (
                    SELECT Source, SUM(PuchaseCount) AS PuchaseCountSum, SUM(WareHouseCount) as WareHouseCount,dbo.fun_GetYearMonth(AddTime) motch  FROM   View_CompanyOrderDetail AS cd   GROUP BY cd.Source,dbo.fun_GetYearMonth(AddTime)
                    ) AS A
                    INNER JOIN (

                    SELECT SortSource, SUM(o.PurchaseCount) AS PuchaseCountSumHospital, SUM(ProcurePrice * PurchaseCount) AS PuchaseCountAmountHospital, dbo.fun_GetYearMonth(AddTime) motch FROM View_ProcureCatalog  v INNER JOIN dbo.TB_PurchaseOrderDetail AS o ON v.ProcureCatalogID = o.ProcureCatalogID
                    GROUP BY  SortSource,dbo.fun_GetYearMonth(AddTime)

                    ) B ON   A.motch=B.motch and A.Source=B.SortSource
                    left join  
                    (
                    select TB_ProcureCatalog.SortSource,SUM(StockCount) StockCount,dbo.fun_GetYearMonth(importtime) montch from TB_CompanyStock ,TB_ProcureCatalog where TB_CompanyStock.ProcureCatalogID=TB_ProcureCatalog.ProcureCatalogID  group by SortSource,dbo.fun_GetYearMonth(importtime)
                    ) c on   A.motch=c.montch and A.Source=c.SortSource order by motch";
                    break;
                case 5:
                    strsql = "   SELECT  A.Source,A.motch,isnull(A.PuchaseCountSum,0) PuchaseCountSum, isnull(A.WareHouseCount,0) WareHouseCount, isnull(B.PuchaseCountAmountHospital,0) PuchaseCountAmountHospital,isnull(B.PuchaseCountSumHospital,0) PuchaseCountSumHospital, isnull(c.StockCount,0)  StockCount,A.BuyName FROM (  SELECT Source, SUM(PuchaseCount) AS PuchaseCountSum, SUM(WareHouseCount) as WareHouseCount,dbo.fun_GetYearMonth(AddTime) motch,BuyId ,BuyName FROM   View_CompanyOrderDetail AS cd  where 1=1  " + where + "   GROUP BY cd.Source,dbo.fun_GetYearMonth(AddTime),BuyId,BuyName  ) AS A   INNER JOIN (  SELECT SortSource, SUM(o.PurchaseCount) AS PuchaseCountSumHospital, SUM(ProcurePrice * PurchaseCount) AS PuchaseCountAmountHospital, dbo.fun_GetYearMonth(AddTime) motch,o.CompanyID_PS FROM View_ProcureCatalog  v INNER JOIN dbo.TB_PurchaseOrderDetail AS o ON v.ProcureCatalogID = o.ProcureCatalogID  GROUP BY  SortSource,dbo.fun_GetYearMonth(AddTime),CompanyID_PS  ) B ON   A.motch=B.motch and A.Source=B.SortSource and A.BuyId=B.CompanyID_PS left join    ( select TB_ProcureCatalog.SortSource,SUM(StockCount) StockCount,dbo.fun_GetYearMonth(importtime) montch,companyid from TB_CompanyStock ,TB_ProcureCatalog where TB_CompanyStock. ProcureCatalogID=TB_ProcureCatalog.ProcureCatalogID  group by SortSource,dbo.fun_GetYearMonth(importtime),companyid ) c on   b.motch=c.montch and b.SortSource=c.SortSource  and b.CompanyID_PS=c.companyid order by "+order;
                    break;
                case 6:
                    strsql = "SELECT A.SortName,  A.Source,A.motch,isnull(A.PuchaseCountSum,0) PuchaseCountSum, isnull(A.WareHouseCount,0) WareHouseCount, isnull(B.PuchaseCountAmountHospital,0) PuchaseCountAmountHospital,isnull(B.PuchaseCountSumHospital,0) PuchaseCountSumHospital, isnull(c.StockCount,0)  StockCount,A.BuyName FROM (  SELECT SortName, Source, SUM(PuchaseCount) AS PuchaseCountSum, SUM(WareHouseCount) as WareHouseCount,dbo.fun_GetYearMonth(AddTime) motch,BuyId ,BuyName FROM   View_CompanyOrderDetail AS cd where 1=1 " + where + "   GROUP BY cd.SortName, cd.Source,dbo.fun_GetYearMonth(AddTime),BuyId,BuyName  ) AS A   INNER JOIN (  SELECT SortSource, SUM(o.PurchaseCount) AS PuchaseCountSumHospital, SUM(ProcurePrice * PurchaseCount) AS PuchaseCountAmountHospital, dbo.fun_GetYearMonth(AddTime) motch,o.CompanyID_PS FROM View_ProcureCatalog  v INNER JOIN dbo.TB_PurchaseOrderDetail AS o ON v.ProcureCatalogID = o.ProcureCatalogID  GROUP BY  SortSource,dbo.fun_GetYearMonth(AddTime),CompanyID_PS  ) B ON   A.motch=B.motch and A.Source=B.SortSource and A.BuyId=B.CompanyID_PS left join    ( select TB_ProcureCatalog.SortSource,SUM(StockCount) StockCount,dbo.fun_GetYearMonth(importtime) montch,companyid from TB_CompanyStock ,TB_ProcureCatalog where TB_CompanyStock. ProcureCatalogID=TB_ProcureCatalog.ProcureCatalogID  group by SortSource,dbo.fun_GetYearMonth(importtime),companyid ) c on   b.motch=c.montch and b.SortSource=c.SortSource  and b.CompanyID_PS=c.companyid order by " + order;
                    break;
            }
            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(strsql, pagesize);
            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;
            return des.GetSpecailPage();
        }


    }
}
